{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#Agenda\n",
      "\n",
      "- Define the problem and the approach\n",
      "- Data basics: loading data, looking at your data, basic commands\n",
      "- Handling missing values\n",
      "- Intro to scikit-learn\n",
      "- <p style=\"color: red\">Grouping and aggregating data</p>\n",
      "- Feature selection\n",
      "- Fitting and evaluating a model\n",
      "- Deploying your work"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#In this workbook you will\n",
      "- Receive and overview of the `apply` function\n",
      "- Write custom functions for analyzing data in `pandas`\n",
      "- Do SQL style joins on your data frames"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "import pylab as pl"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_csv(\"./data/credit-data-trainingset.csv\")\n",
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>serious_dlqin2yrs</th>\n",
        "      <th>revolving_utilization_of_unsecured_lines</th>\n",
        "      <th>age</th>\n",
        "      <th>number_of_time30-59_days_past_due_not_worse</th>\n",
        "      <th>debt_ratio</th>\n",
        "      <th>monthly_income</th>\n",
        "      <th>number_of_open_credit_lines_and_loans</th>\n",
        "      <th>number_of_times90_days_late</th>\n",
        "      <th>number_real_estate_loans_or_lines</th>\n",
        "      <th>number_of_time60-89_days_past_due_not_worse</th>\n",
        "      <th>number_of_dependents</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0.658180</td>\n",
        "      <td> 38</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0.085113</td>\n",
        "      <td>  3042</td>\n",
        "      <td> 2</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0.233810</td>\n",
        "      <td> 30</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.036050</td>\n",
        "      <td>  3300</td>\n",
        "      <td> 5</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0.907239</td>\n",
        "      <td> 49</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0.024926</td>\n",
        "      <td> 63588</td>\n",
        "      <td> 7</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0.754464</td>\n",
        "      <td> 39</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.209940</td>\n",
        "      <td>  3500</td>\n",
        "      <td> 8</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 0</td>\n",
        "      <td> 0.189169</td>\n",
        "      <td> 57</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0.606291</td>\n",
        "      <td> 23684</td>\n",
        "      <td> 9</td>\n",
        "      <td> 0</td>\n",
        "      <td> 4</td>\n",
        "      <td> 0</td>\n",
        "      <td> 2</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "   serious_dlqin2yrs  revolving_utilization_of_unsecured_lines  age  \\\n",
        "0                  0                                  0.658180   38   \n",
        "1                  0                                  0.233810   30   \n",
        "2                  0                                  0.907239   49   \n",
        "3                  0                                  0.754464   39   \n",
        "4                  0                                  0.189169   57   \n",
        "\n",
        "   number_of_time30-59_days_past_due_not_worse  debt_ratio  monthly_income  \\\n",
        "0                                            1    0.085113            3042   \n",
        "1                                            0    0.036050            3300   \n",
        "2                                            1    0.024926           63588   \n",
        "3                                            0    0.209940            3500   \n",
        "4                                            0    0.606291           23684   \n",
        "\n",
        "   number_of_open_credit_lines_and_loans  number_of_times90_days_late  \\\n",
        "0                                      2                            1   \n",
        "1                                      5                            0   \n",
        "2                                      7                            0   \n",
        "3                                      8                            0   \n",
        "4                                      9                            0   \n",
        "\n",
        "   number_real_estate_loans_or_lines  \\\n",
        "0                                  0   \n",
        "1                                  0   \n",
        "2                                  1   \n",
        "3                                  0   \n",
        "4                                  4   \n",
        "\n",
        "   number_of_time60-89_days_past_due_not_worse  number_of_dependents  \n",
        "0                                            0                     0  \n",
        "1                                            0                     0  \n",
        "2                                            0                     0  \n",
        "3                                            0                     0  \n",
        "4                                            0                     2  "
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Apply\n",
      "\"Applies\" or operates on a column in your data frame with a given function. This is analagous to an Excel formula."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.monthly_income.apply(np.log)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "0      8.020270\n",
        "1      8.101678\n",
        "2     11.060180\n",
        "3      8.160518\n",
        "4     10.072555\n",
        "5      7.824046\n",
        "6      8.779711\n",
        "7      9.429797\n",
        "8      9.525151\n",
        "9          -inf\n",
        "10     9.082507\n",
        "11     8.095599\n",
        "12     5.808142\n",
        "13     9.417355\n",
        "14     8.006368\n",
        "...\n",
        "112904    6.775366\n",
        "112905    0.000000\n",
        "112906    4.672829\n",
        "112907        -inf\n",
        "112908    0.000000\n",
        "112909        -inf\n",
        "112910        -inf\n",
        "112911    0.000000\n",
        "112912        -inf\n",
        "112913    0.000000\n",
        "112914        -inf\n",
        "112915        -inf\n",
        "112916    0.000000\n",
        "112917        -inf\n",
        "112918        -inf\n",
        "Name: monthly_income, Length: 112919"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###Applying with lambda functions\n",
      "A `lambda` function is an anonymous function. Think of it just as a shorthand way to define a quick function that you need once."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "add_10 = lambda x: x + 10\n",
      "plus = lambda x, y: x + y\n",
      "\n",
      "print add_10(9)\n",
      "print plus(10, 20)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "19\n",
        "30\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.monthly_income.apply(lambda x: np.log(x + 1))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "0      8.020599\n",
        "1      8.101981\n",
        "2     11.060196\n",
        "3      8.160804\n",
        "4     10.072597\n",
        "5      7.824446\n",
        "6      8.779865\n",
        "7      9.429877\n",
        "8      9.525224\n",
        "9      0.000000\n",
        "10     9.082621\n",
        "11     8.095904\n",
        "12     5.811141\n",
        "13     9.417436\n",
        "14     8.006701\n",
        "...\n",
        "112904    6.776507\n",
        "112905    0.693147\n",
        "112906    4.682131\n",
        "112907    0.000000\n",
        "112908    0.693147\n",
        "112909    0.000000\n",
        "112910    0.000000\n",
        "112911    0.693147\n",
        "112912    0.000000\n",
        "112913    0.693147\n",
        "112914    0.000000\n",
        "112915    0.000000\n",
        "112916    0.693147\n",
        "112917    0.000000\n",
        "112918    0.000000\n",
        "Name: monthly_income, Length: 112919"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#numpy actually has log(x + 1)\n",
      "help(np.log1p)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "Help on ufunc object:\n",
        "\n",
        "log1p = class ufunc(__builtin__.object)\n",
        " |  Functions that operate element by element on whole arrays.\n",
        " |  \n",
        " |  To see the documentation for a specific ufunc, use np.info().  For\n",
        " |  example, np.info(np.sin).  Because ufuncs are written in C\n",
        " |  (for speed) and linked into Python with NumPy's ufunc facility,\n",
        " |  Python's help() function finds this page whenever help() is called\n",
        " |  on a ufunc.\n",
        " |  \n",
        " |  A detailed explanation of ufuncs can be found in the \"ufuncs.rst\"\n",
        " |  file in the NumPy reference guide.\n",
        " |  \n",
        " |  Unary ufuncs:\n",
        " |  =============\n",
        " |  \n",
        " |  op(X, out=None)\n",
        " |  Apply op to X elementwise\n",
        " |  \n",
        " |  Parameters\n",
        " |  ----------\n",
        " |  X : array_like\n",
        " |      Input array.\n",
        " |  out : array_like\n",
        " |      An array to store the output. Must be the same shape as `X`.\n",
        " |  \n",
        " |  Returns\n",
        " |  -------\n",
        " |  r : array_like\n",
        " |      `r` will have the same shape as `X`; if out is provided, `r`\n",
        " |      will be equal to out.\n",
        " |  \n",
        " |  Binary ufuncs:\n",
        " |  ==============\n",
        " |  \n",
        " |  op(X, Y, out=None)\n",
        " |  Apply `op` to `X` and `Y` elementwise. May \"broadcast\" to make\n",
        " |  the shapes of `X` and `Y` congruent.\n",
        " |  \n",
        " |  The broadcasting rules are:\n",
        " |  \n",
        " |  * Dimensions of length 1 may be prepended to either array.\n",
        " |  * Arrays may be repeated along dimensions of length 1.\n",
        " |  \n",
        " |  Parameters\n",
        " |  ----------\n",
        " |  X : array_like\n",
        " |      First input array.\n",
        " |  Y : array_like\n",
        " |      Second input array.\n",
        " |  out : array_like\n",
        " |      An array to store the output. Must be the same shape as the\n",
        " |      output would have.\n",
        " |  \n",
        " |  Returns\n",
        " |  -------\n",
        " |  r : array_like\n",
        " |      The return value; if out is provided, `r` will be equal to out.\n",
        " |  \n",
        " |  Methods defined here:\n",
        " |  \n",
        " |  __call__(...)\n",
        " |      x.__call__(...) <==> x(...)\n",
        " |  \n",
        " |  __repr__(...)\n",
        " |      x.__repr__() <==> repr(x)\n",
        " |  \n",
        " |  __str__(...)\n",
        " |      x.__str__() <==> str(x)\n",
        " |  \n",
        " |  accumulate(...)\n",
        " |      accumulate(array, axis=0, dtype=None, out=None)\n",
        " |      \n",
        " |      Accumulate the result of applying the operator to all elements.\n",
        " |      \n",
        " |      For a one-dimensional array, accumulate produces results equivalent to::\n",
        " |      \n",
        " |        r = np.empty(len(A))\n",
        " |        t = op.identity        # op = the ufunc being applied to A's  elements\n",
        " |        for i in xrange(len(A)):\n",
        " |            t = op(t, A[i])\n",
        " |            r[i] = t\n",
        " |        return r\n",
        " |      \n",
        " |      For example, add.accumulate() is equivalent to np.cumsum().\n",
        " |      \n",
        " |      For a multi-dimensional array, accumulate is applied along only one\n",
        " |      axis (axis zero by default; see Examples below) so repeated use is\n",
        " |      necessary if one wants to accumulate over multiple axes.\n",
        " |      \n",
        " |      Parameters\n",
        " |      ----------\n",
        " |      array : array_like\n",
        " |          The array to act on.\n",
        " |      axis : int, optional\n",
        " |          The axis along which to apply the accumulation; default is zero.\n",
        " |      dtype : data-type code, optional\n",
        " |          The data-type used to represent the intermediate results. Defaults\n",
        " |          to the data-type of the output array if such is provided, or the\n",
        " |          the data-type of the input array if no output array is provided.\n",
        " |      out : ndarray, optional\n",
        " |          A location into which the result is stored. If not provided a\n",
        " |          freshly-allocated array is returned.\n",
        " |      \n",
        " |      Returns\n",
        " |      -------\n",
        " |      r : ndarray\n",
        " |          The accumulated values. If `out` was supplied, `r` is a reference to\n",
        " |          `out`.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      1-D array examples:\n",
        " |      \n",
        " |      >>> np.add.accumulate([2, 3, 5])\n",
        " |      array([ 2,  5, 10])\n",
        " |      >>> np.multiply.accumulate([2, 3, 5])\n",
        " |      array([ 2,  6, 30])\n",
        " |      \n",
        " |      2-D array examples:\n",
        " |      \n",
        " |      >>> I = np.eye(2)\n",
        " |      >>> I\n",
        " |      array([[ 1.,  0.],\n",
        " |             [ 0.,  1.]])\n",
        " |      \n",
        " |      Accumulate along axis 0 (rows), down columns:\n",
        " |      \n",
        " |      >>> np.add.accumulate(I, 0)\n",
        " |      array([[ 1.,  0.],\n",
        " |             [ 1.,  1.]])\n",
        " |      >>> np.add.accumulate(I) # no axis specified = axis zero\n",
        " |      array([[ 1.,  0.],\n",
        " |             [ 1.,  1.]])\n",
        " |      \n",
        " |      Accumulate along axis 1 (columns), through rows:\n",
        " |      \n",
        " |      >>> np.add.accumulate(I, 1)\n",
        " |      array([[ 1.,  1.],\n",
        " |             [ 0.,  1.]])\n",
        " |  \n",
        " |  outer(...)\n",
        " |      outer(A, B)\n",
        " |      \n",
        " |      Apply the ufunc `op` to all pairs (a, b) with a in `A` and b in `B`.\n",
        " |      \n",
        " |      Let ``M = A.ndim``, ``N = B.ndim``. Then the result, `C`, of\n",
        " |      ``op.outer(A, B)`` is an array of dimension M + N such that:\n",
        " |      \n",
        " |      .. math:: C[i_0, ..., i_{M-1}, j_0, ..., j_{N-1}] =\n",
        " |         op(A[i_0, ..., i_{M-1}], B[j_0, ..., j_{N-1}])\n",
        " |      \n",
        " |      For `A` and `B` one-dimensional, this is equivalent to::\n",
        " |      \n",
        " |        r = empty(len(A),len(B))\n",
        " |        for i in xrange(len(A)):\n",
        " |            for j in xrange(len(B)):\n",
        " |                r[i,j] = op(A[i], B[j]) # op = ufunc in question\n",
        " |      \n",
        " |      Parameters\n",
        " |      ----------\n",
        " |      A : array_like\n",
        " |          First array\n",
        " |      B : array_like\n",
        " |          Second array\n",
        " |      \n",
        " |      Returns\n",
        " |      -------\n",
        " |      r : ndarray\n",
        " |          Output array\n",
        " |      \n",
        " |      See Also\n",
        " |      --------\n",
        " |      numpy.outer\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.multiply.outer([1, 2, 3], [4, 5, 6])\n",
        " |      array([[ 4,  5,  6],\n",
        " |             [ 8, 10, 12],\n",
        " |             [12, 15, 18]])\n",
        " |      \n",
        " |      A multi-dimensional example:\n",
        " |      \n",
        " |      >>> A = np.array([[1, 2, 3], [4, 5, 6]])\n",
        " |      >>> A.shape\n",
        " |      (2, 3)\n",
        " |      >>> B = np.array([[1, 2, 3, 4]])\n",
        " |      >>> B.shape\n",
        " |      (1, 4)\n",
        " |      >>> C = np.multiply.outer(A, B)\n",
        " |      >>> C.shape; C\n",
        " |      (2, 3, 1, 4)\n",
        " |      array([[[[ 1,  2,  3,  4]],\n",
        " |              [[ 2,  4,  6,  8]],\n",
        " |              [[ 3,  6,  9, 12]]],\n",
        " |             [[[ 4,  8, 12, 16]],\n",
        " |              [[ 5, 10, 15, 20]],\n",
        " |              [[ 6, 12, 18, 24]]]])\n",
        " |  \n",
        " |  reduce(...)\n",
        " |      reduce(a, axis=0, dtype=None, out=None)\n",
        " |      \n",
        " |      Reduces `a`'s dimension by one, by applying ufunc along one axis.\n",
        " |      \n",
        " |      Let :math:`a.shape = (N_0, ..., N_i, ..., N_{M-1})`.  Then\n",
        " |      :math:`ufunc.reduce(a, axis=i)[k_0, ..,k_{i-1}, k_{i+1}, .., k_{M-1}]` =\n",
        " |      the result of iterating `j` over :math:`range(N_i)`, cumulatively applying\n",
        " |      ufunc to each :math:`a[k_0, ..,k_{i-1}, j, k_{i+1}, .., k_{M-1}]`.\n",
        " |      For a one-dimensional array, reduce produces results equivalent to:\n",
        " |      ::\n",
        " |      \n",
        " |       r = op.identity # op = ufunc\n",
        " |       for i in xrange(len(A)):\n",
        " |         r = op(r, A[i])\n",
        " |       return r\n",
        " |      \n",
        " |      For example, add.reduce() is equivalent to sum().\n",
        " |      \n",
        " |      Parameters\n",
        " |      ----------\n",
        " |      a : array_like\n",
        " |          The array to act on.\n",
        " |      axis : int, optional\n",
        " |          The axis along which to apply the reduction.\n",
        " |      dtype : data-type code, optional\n",
        " |          The type used to represent the intermediate results. Defaults\n",
        " |          to the data-type of the output array if this is provided, or\n",
        " |          the data-type of the input array if no output array is provided.\n",
        " |      out : ndarray, optional\n",
        " |          A location into which the result is stored. If not provided, a\n",
        " |          freshly-allocated array is returned.\n",
        " |      \n",
        " |      Returns\n",
        " |      -------\n",
        " |      r : ndarray\n",
        " |          The reduced array. If `out` was supplied, `r` is a reference to it.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.multiply.reduce([2,3,5])\n",
        " |      30\n",
        " |      \n",
        " |      A multi-dimensional array example:\n",
        " |      \n",
        " |      >>> X = np.arange(8).reshape((2,2,2))\n",
        " |      >>> X\n",
        " |      array([[[0, 1],\n",
        " |              [2, 3]],\n",
        " |             [[4, 5],\n",
        " |              [6, 7]]])\n",
        " |      >>> np.add.reduce(X, 0)\n",
        " |      array([[ 4,  6],\n",
        " |             [ 8, 10]])\n",
        " |      >>> np.add.reduce(X) # confirm: default axis value is 0\n",
        " |      array([[ 4,  6],\n",
        " |             [ 8, 10]])\n",
        " |      >>> np.add.reduce(X, 1)\n",
        " |      array([[ 2,  4],\n",
        " |             [10, 12]])\n",
        " |      >>> np.add.reduce(X, 2)\n",
        " |      array([[ 1,  5],\n",
        " |             [ 9, 13]])\n",
        " |  \n",
        " |  reduceat(...)\n",
        " |      reduceat(a, indices, axis=0, dtype=None, out=None)\n",
        " |      \n",
        " |      Performs a (local) reduce with specified slices over a single axis.\n",
        " |      \n",
        " |      For i in ``range(len(indices))``, `reduceat` computes\n",
        " |      ``ufunc.reduce(a[indices[i]:indices[i+1]])``, which becomes the i-th\n",
        " |      generalized \"row\" parallel to `axis` in the final result (i.e., in a\n",
        " |      2-D array, for example, if `axis = 0`, it becomes the i-th row, but if\n",
        " |      `axis = 1`, it becomes the i-th column).  There are two exceptions to this:\n",
        " |      \n",
        " |        * when ``i = len(indices) - 1`` (so for the last index),\n",
        " |          ``indices[i+1] = a.shape[axis]``.\n",
        " |        * if ``indices[i] >= indices[i + 1]``, the i-th generalized \"row\" is\n",
        " |          simply ``a[indices[i]]``.\n",
        " |      \n",
        " |      The shape of the output depends on the size of `indices`, and may be\n",
        " |      larger than `a` (this happens if ``len(indices) > a.shape[axis]``).\n",
        " |      \n",
        " |      Parameters\n",
        " |      ----------\n",
        " |      a : array_like\n",
        " |          The array to act on.\n",
        " |      indices : array_like\n",
        " |          Paired indices, comma separated (not colon), specifying slices to\n",
        " |          reduce.\n",
        " |      axis : int, optional\n",
        " |          The axis along which to apply the reduceat.\n",
        " |      dtype : data-type code, optional\n",
        " |          The type used to represent the intermediate results. Defaults\n",
        " |          to the data type of the output array if this is provided, or\n",
        " |          the data type of the input array if no output array is provided.\n",
        " |      out : ndarray, optional\n",
        " |          A location into which the result is stored. If not provided a\n",
        " |          freshly-allocated array is returned.\n",
        " |      \n",
        " |      Returns\n",
        " |      -------\n",
        " |      r : ndarray\n",
        " |          The reduced values. If `out` was supplied, `r` is a reference to\n",
        " |          `out`.\n",
        " |      \n",
        " |      Notes\n",
        " |      -----\n",
        " |      A descriptive example:\n",
        " |      \n",
        " |      If `a` is 1-D, the function `ufunc.accumulate(a)` is the same as\n",
        " |      ``ufunc.reduceat(a, indices)[::2]`` where `indices` is\n",
        " |      ``range(len(array) - 1)`` with a zero placed\n",
        " |      in every other element:\n",
        " |      ``indices = zeros(2 * len(a) - 1)``, ``indices[1::2] = range(1, len(a))``.\n",
        " |      \n",
        " |      Don't be fooled by this attribute's name: `reduceat(a)` is not\n",
        " |      necessarily smaller than `a`.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      To take the running sum of four successive values:\n",
        " |      \n",
        " |      >>> np.add.reduceat(np.arange(8),[0,4, 1,5, 2,6, 3,7])[::2]\n",
        " |      array([ 6, 10, 14, 18])\n",
        " |      \n",
        " |      A 2-D example:\n",
        " |      \n",
        " |      >>> x = np.linspace(0, 15, 16).reshape(4,4)\n",
        " |      >>> x\n",
        " |      array([[  0.,   1.,   2.,   3.],\n",
        " |             [  4.,   5.,   6.,   7.],\n",
        " |             [  8.,   9.,  10.,  11.],\n",
        " |             [ 12.,  13.,  14.,  15.]])\n",
        " |      \n",
        " |      ::\n",
        " |      \n",
        " |       # reduce such that the result has the following five rows:\n",
        " |       # [row1 + row2 + row3]\n",
        " |       # [row4]\n",
        " |       # [row2]\n",
        " |       # [row3]\n",
        " |       # [row1 + row2 + row3 + row4]\n",
        " |      \n",
        " |      >>> np.add.reduceat(x, [0, 3, 1, 2, 0])\n",
        " |      array([[ 12.,  15.,  18.,  21.],\n",
        " |             [ 12.,  13.,  14.,  15.],\n",
        " |             [  4.,   5.,   6.,   7.],\n",
        " |             [  8.,   9.,  10.,  11.],\n",
        " |             [ 24.,  28.,  32.,  36.]])\n",
        " |      \n",
        " |      ::\n",
        " |      \n",
        " |       # reduce such that result has the following two columns:\n",
        " |       # [col1 * col2 * col3, col4]\n",
        " |      \n",
        " |      >>> np.multiply.reduceat(x, [0, 3], 1)\n",
        " |      array([[    0.,     3.],\n",
        " |             [  120.,     7.],\n",
        " |             [  720.,    11.],\n",
        " |             [ 2184.,    15.]])\n",
        " |  \n",
        " |  ----------------------------------------------------------------------\n",
        " |  Data descriptors defined here:\n",
        " |  \n",
        " |  identity\n",
        " |      The identity value.\n",
        " |      \n",
        " |      Data attribute containing the identity element for the ufunc, if it has one.\n",
        " |      If it does not, the attribute value is None.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.identity\n",
        " |      0\n",
        " |      >>> np.multiply.identity\n",
        " |      1\n",
        " |      >>> np.power.identity\n",
        " |      1\n",
        " |      >>> print np.exp.identity\n",
        " |      None\n",
        " |  \n",
        " |  nargs\n",
        " |      The number of arguments.\n",
        " |      \n",
        " |      Data attribute containing the number of arguments the ufunc takes, including\n",
        " |      optional ones.\n",
        " |      \n",
        " |      Notes\n",
        " |      -----\n",
        " |      Typically this value will be one more than what you might expect because all\n",
        " |      ufuncs take  the optional \"out\" argument.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.nargs\n",
        " |      3\n",
        " |      >>> np.multiply.nargs\n",
        " |      3\n",
        " |      >>> np.power.nargs\n",
        " |      3\n",
        " |      >>> np.exp.nargs\n",
        " |      2\n",
        " |  \n",
        " |  nin\n",
        " |      The number of inputs.\n",
        " |      \n",
        " |      Data attribute containing the number of arguments the ufunc treats as input.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.nin\n",
        " |      2\n",
        " |      >>> np.multiply.nin\n",
        " |      2\n",
        " |      >>> np.power.nin\n",
        " |      2\n",
        " |      >>> np.exp.nin\n",
        " |      1\n",
        " |  \n",
        " |  nout\n",
        " |      The number of outputs.\n",
        " |      \n",
        " |      Data attribute containing the number of arguments the ufunc treats as output.\n",
        " |      \n",
        " |      Notes\n",
        " |      -----\n",
        " |      Since all ufuncs can take output arguments, this will always be (at least) 1.\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.nout\n",
        " |      1\n",
        " |      >>> np.multiply.nout\n",
        " |      1\n",
        " |      >>> np.power.nout\n",
        " |      1\n",
        " |      >>> np.exp.nout\n",
        " |      1\n",
        " |  \n",
        " |  ntypes\n",
        " |      The number of types.\n",
        " |      \n",
        " |      The number of numerical NumPy types - of which there are 18 total - on which\n",
        " |      the ufunc can operate.\n",
        " |      \n",
        " |      See Also\n",
        " |      --------\n",
        " |      numpy.ufunc.types\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.ntypes\n",
        " |      18\n",
        " |      >>> np.multiply.ntypes\n",
        " |      18\n",
        " |      >>> np.power.ntypes\n",
        " |      17\n",
        " |      >>> np.exp.ntypes\n",
        " |      7\n",
        " |      >>> np.remainder.ntypes\n",
        " |      14\n",
        " |  \n",
        " |  signature\n",
        " |  \n",
        " |  types\n",
        " |      Returns a list with types grouped input->output.\n",
        " |      \n",
        " |      Data attribute listing the data-type \"Domain-Range\" groupings the ufunc can\n",
        " |      deliver. The data-types are given using the character codes.\n",
        " |      \n",
        " |      See Also\n",
        " |      --------\n",
        " |      numpy.ufunc.ntypes\n",
        " |      \n",
        " |      Examples\n",
        " |      --------\n",
        " |      >>> np.add.types\n",
        " |      ['??->?', 'bb->b', 'BB->B', 'hh->h', 'HH->H', 'ii->i', 'II->I', 'll->l',\n",
        " |      'LL->L', 'qq->q', 'QQ->Q', 'ff->f', 'dd->d', 'gg->g', 'FF->F', 'DD->D',\n",
        " |      'GG->G', 'OO->O']\n",
        " |      \n",
        " |      >>> np.multiply.types\n",
        " |      ['??->?', 'bb->b', 'BB->B', 'hh->h', 'HH->H', 'ii->i', 'II->I', 'll->l',\n",
        " |      'LL->L', 'qq->q', 'QQ->Q', 'ff->f', 'dd->d', 'gg->g', 'FF->F', 'DD->D',\n",
        " |      'GG->G', 'OO->O']\n",
        " |      \n",
        " |      >>> np.power.types\n",
        " |      ['bb->b', 'BB->B', 'hh->h', 'HH->H', 'ii->i', 'II->I', 'll->l', 'LL->L',\n",
        " |      'qq->q', 'QQ->Q', 'ff->f', 'dd->d', 'gg->g', 'FF->F', 'DD->D', 'GG->G',\n",
        " |      'OO->O']\n",
        " |      \n",
        " |      >>> np.exp.types\n",
        " |      ['f->f', 'd->d', 'g->g', 'F->F', 'D->D', 'G->G', 'O->O']\n",
        " |      \n",
        " |      >>> np.remainder.types\n",
        " |      ['bb->b', 'BB->B', 'hh->h', 'HH->H', 'ii->i', 'II->I', 'll->l', 'LL->L',\n",
        " |      'qq->q', 'QQ->Q', 'ff->f', 'dd->d', 'gg->g', 'OO->O']\n",
        "\n"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###Using custom functions\n",
      "If you can't do it in a one-liner lambda function don't worry. `pandas` also let's `apply` your own custom functions. You can use custom functions when applying on Series and also when operating on chunks of data frames in `groupby`s."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def inverse(x):\n",
      "    return 1 / (x + 1)\n",
      "\n",
      "df.monthly_income.apply(inverse)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "0     0.000329\n",
        "1     0.000303\n",
        "2     0.000016\n",
        "3     0.000286\n",
        "4     0.000042\n",
        "5     0.000400\n",
        "6     0.000154\n",
        "7     0.000080\n",
        "8     0.000073\n",
        "9     1.000000\n",
        "10    0.000114\n",
        "11    0.000305\n",
        "12    0.002994\n",
        "13    0.000081\n",
        "14    0.000333\n",
        "...\n",
        "112904    0.001140\n",
        "112905    0.500000\n",
        "112906    0.009259\n",
        "112907    1.000000\n",
        "112908    0.500000\n",
        "112909    1.000000\n",
        "112910    1.000000\n",
        "112911    0.500000\n",
        "112912    1.000000\n",
        "112913    0.500000\n",
        "112914    1.000000\n",
        "112915    1.000000\n",
        "112916    0.500000\n",
        "112917    1.000000\n",
        "112918    1.000000\n",
        "Name: monthly_income, Length: 112919"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####Write a custom function called `cap_value(x, cap)` that will set x to the cap if x > cap. Then apply it to debt_ratio with a cap of 5."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#DELETEdef cap_value(x, cap):\n",
      "#    if x > cap:\n",
      "#        return cap\n",
      "#    else:\n",
      "#        return x\n",
      "\n",
      "def cap_value(x, cap):\n",
      "    \"\"\"\n",
      "    x - a value\n",
      "    cap - threshold value for x; if x > cap, then x is set to cap\n",
      "    Examples:\n",
      "        cap_value(1000, 10)\n",
      "        10\n",
      "        cap_value(10, 100)\n",
      "        10\n",
      "        \"\"\"\n",
      "    # your code here\n",
      "    return None\n",
      "print cap_value(1000, 10)==10\n",
      "print cap_value(10, 100)==10\n",
      "print df.debt_ratio.apply(lambda x: cap_value(x, 5.0)).mean()#should be close to 1.28"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "True\n",
        "True\n",
        "1.28283867571"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Split -> Apply ->Combine\n",
      "Split, Apply, Combine is a data munging methodology similar in spirit to `SQL`'s `GROUP BY`. The idea being you split your data into chunks, operate on those chunks, and then combine the results together into a single table. `groupby` in `pandas` works exactly the same way. But since we're using Python and not SQL, we have a lot more flexibility in terms of the types of operations we can perform in the apply step.\n",
      "\n",
      "From the `pandas` documentation:\n",
      "\n",
      " - Splitting the data into groups based on some criteria\n",
      " - Applying a function to each group independently\n",
      " - Combining the results into a data structure"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###Split"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "subset = df[['serious_dlqin2yrs', 'age', 'monthly_income']]\n",
      "subset.groupby(\"serious_dlqin2yrs\")"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "<pandas.core.groupby.DataFrameGroupBy at 0x107a532d0>"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###Apply / Combine\n",
      "Aggregate whatever is returned"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "subset.groupby(\"serious_dlqin2yrs\").mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>age</th>\n",
        "      <th>monthly_income</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>serious_dlqin2yrs</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 52.742980</td>\n",
        "      <td> 5463.104630</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 45.940302</td>\n",
        "      <td> 4747.533165</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 18,
       "text": [
        "                         age  monthly_income\n",
        "serious_dlqin2yrs                           \n",
        "0                  52.742980     5463.104630\n",
        "1                  45.940302     4747.533165"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "What's really going on here? You can see below that when you `groupby` a certain variable(s), you're literally splitting the data into chunks based on each possible value of that variable."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for name, group in subset.groupby(\"serious_dlqin2yrs\"):\n",
      "    print \"splitting by: \", name\n",
      "    print group.mean()\n",
      "    print \"*\"*80"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "splitting by:  0\n",
        "serious_dlqin2yrs       0.00000\n",
        "age                    52.74298\n",
        "monthly_income       5463.10463\n",
        "********************************************************************************\n",
        "splitting by:  1\n",
        "serious_dlqin2yrs       1.000000\n",
        "age                    45.940302\n",
        "monthly_income       4747.533165\n",
        "********************************************************************************\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####Use groupby to calculate the percent of customers that went bad for each age"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#DELETEsubset[['age', 'serious_dlqin2yrs']].groupby(\"age\").mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>serious_dlqin2yrs</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>age</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>21 </th>\n",
        "      <td> 0.069231</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>22 </th>\n",
        "      <td> 0.074405</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>23 </th>\n",
        "      <td> 0.108163</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>24 </th>\n",
        "      <td> 0.114238</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>25 </th>\n",
        "      <td> 0.129395</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>26 </th>\n",
        "      <td> 0.116869</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>27 </th>\n",
        "      <td> 0.120603</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>28 </th>\n",
        "      <td> 0.124785</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>29 </th>\n",
        "      <td> 0.106615</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30 </th>\n",
        "      <td> 0.110811</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>31 </th>\n",
        "      <td> 0.099553</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>32 </th>\n",
        "      <td> 0.114509</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33 </th>\n",
        "      <td> 0.112634</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>34 </th>\n",
        "      <td> 0.101281</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>35 </th>\n",
        "      <td> 0.114687</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>36 </th>\n",
        "      <td> 0.092789</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>37 </th>\n",
        "      <td> 0.092534</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>38 </th>\n",
        "      <td> 0.087774</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>39 </th>\n",
        "      <td> 0.098048</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>40 </th>\n",
        "      <td> 0.087325</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>41 </th>\n",
        "      <td> 0.099873</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>42 </th>\n",
        "      <td> 0.087538</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>43 </th>\n",
        "      <td> 0.085833</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>44 </th>\n",
        "      <td> 0.072800</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>45 </th>\n",
        "      <td> 0.081794</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>46 </th>\n",
        "      <td> 0.089509</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>47 </th>\n",
        "      <td> 0.079829</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>48 </th>\n",
        "      <td> 0.069759</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>49 </th>\n",
        "      <td> 0.082076</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>50 </th>\n",
        "      <td> 0.076814</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>51 </th>\n",
        "      <td> 0.074835</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>52 </th>\n",
        "      <td> 0.072421</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>53 </th>\n",
        "      <td> 0.073657</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>54 </th>\n",
        "      <td> 0.069465</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>55 </th>\n",
        "      <td> 0.064679</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>56 </th>\n",
        "      <td> 0.058497</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>57 </th>\n",
        "      <td> 0.053250</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>58 </th>\n",
        "      <td> 0.048480</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>59 </th>\n",
        "      <td> 0.050000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>60 </th>\n",
        "      <td> 0.045214</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>61 </th>\n",
        "      <td> 0.050395</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>62 </th>\n",
        "      <td> 0.046750</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>63 </th>\n",
        "      <td> 0.033014</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>64 </th>\n",
        "      <td> 0.030078</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>65 </th>\n",
        "      <td> 0.039474</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>66 </th>\n",
        "      <td> 0.030769</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>67 </th>\n",
        "      <td> 0.033546</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>68 </th>\n",
        "      <td> 0.021779</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>69 </th>\n",
        "      <td> 0.018330</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>70 </th>\n",
        "      <td> 0.032787</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>71 </th>\n",
        "      <td> 0.025748</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>72 </th>\n",
        "      <td> 0.026899</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>73 </th>\n",
        "      <td> 0.026910</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>74 </th>\n",
        "      <td> 0.026197</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>75 </th>\n",
        "      <td> 0.019129</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>76 </th>\n",
        "      <td> 0.021229</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>77 </th>\n",
        "      <td> 0.017115</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>78 </th>\n",
        "      <td> 0.024548</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>79 </th>\n",
        "      <td> 0.024032</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>80 </th>\n",
        "      <td> 0.017493</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>81 </th>\n",
        "      <td> 0.010563</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>82 </th>\n",
        "      <td> 0.029046</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>83 </th>\n",
        "      <td> 0.023622</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>84 </th>\n",
        "      <td> 0.014286</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>85 </th>\n",
        "      <td> 0.010811</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>86 </th>\n",
        "      <td> 0.018750</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>87 </th>\n",
        "      <td> 0.018519</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>88 </th>\n",
        "      <td> 0.025974</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>89 </th>\n",
        "      <td> 0.033333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>90 </th>\n",
        "      <td> 0.014184</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>91 </th>\n",
        "      <td> 0.034188</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>92 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>93 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>94 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>95 </th>\n",
        "      <td> 0.033333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>96 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>97 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>98 </th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>99 </th>\n",
        "      <td> 0.125000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>101</th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>102</th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>103</th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>109</th>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 20,
       "text": [
        "     serious_dlqin2yrs\n",
        "age                   \n",
        "21            0.069231\n",
        "22            0.074405\n",
        "23            0.108163\n",
        "24            0.114238\n",
        "25            0.129395\n",
        "26            0.116869\n",
        "27            0.120603\n",
        "28            0.124785\n",
        "29            0.106615\n",
        "30            0.110811\n",
        "31            0.099553\n",
        "32            0.114509\n",
        "33            0.112634\n",
        "34            0.101281\n",
        "35            0.114687\n",
        "36            0.092789\n",
        "37            0.092534\n",
        "38            0.087774\n",
        "39            0.098048\n",
        "40            0.087325\n",
        "41            0.099873\n",
        "42            0.087538\n",
        "43            0.085833\n",
        "44            0.072800\n",
        "45            0.081794\n",
        "46            0.089509\n",
        "47            0.079829\n",
        "48            0.069759\n",
        "49            0.082076\n",
        "50            0.076814\n",
        "51            0.074835\n",
        "52            0.072421\n",
        "53            0.073657\n",
        "54            0.069465\n",
        "55            0.064679\n",
        "56            0.058497\n",
        "57            0.053250\n",
        "58            0.048480\n",
        "59            0.050000\n",
        "60            0.045214\n",
        "61            0.050395\n",
        "62            0.046750\n",
        "63            0.033014\n",
        "64            0.030078\n",
        "65            0.039474\n",
        "66            0.030769\n",
        "67            0.033546\n",
        "68            0.021779\n",
        "69            0.018330\n",
        "70            0.032787\n",
        "71            0.025748\n",
        "72            0.026899\n",
        "73            0.026910\n",
        "74            0.026197\n",
        "75            0.019129\n",
        "76            0.021229\n",
        "77            0.017115\n",
        "78            0.024548\n",
        "79            0.024032\n",
        "80            0.017493\n",
        "81            0.010563\n",
        "82            0.029046\n",
        "83            0.023622\n",
        "84            0.014286\n",
        "85            0.010811\n",
        "86            0.018750\n",
        "87            0.018519\n",
        "88            0.025974\n",
        "89            0.033333\n",
        "90            0.014184\n",
        "91            0.034188\n",
        "92            0.000000\n",
        "93            0.000000\n",
        "94            0.000000\n",
        "95            0.033333\n",
        "96            0.000000\n",
        "97            0.000000\n",
        "98            0.000000\n",
        "99            0.125000\n",
        "101           0.000000\n",
        "102           0.000000\n",
        "103           0.000000\n",
        "109           0.000000"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###You can also aggregate by multiple functions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "subset.groupby(\"serious_dlqin2yrs\").agg([np.min, np.mean, np.median, np.max])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr>\n",
        "      <th></th>\n",
        "      <th colspan=\"4\" halign=\"left\">age</th>\n",
        "      <th colspan=\"4\" halign=\"left\">monthly_income</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th></th>\n",
        "      <th>amin</th>\n",
        "      <th>mean</th>\n",
        "      <th>median</th>\n",
        "      <th>amax</th>\n",
        "      <th>amin</th>\n",
        "      <th>mean</th>\n",
        "      <th>median</th>\n",
        "      <th>amax</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>serious_dlqin2yrs</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 21</td>\n",
        "      <td> 52.742980</td>\n",
        "      <td> 52</td>\n",
        "      <td> 109</td>\n",
        "      <td> 0</td>\n",
        "      <td> 5463.104630</td>\n",
        "      <td> 4500.0</td>\n",
        "      <td> 1794060</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 21</td>\n",
        "      <td> 45.940302</td>\n",
        "      <td> 45</td>\n",
        "      <td>  99</td>\n",
        "      <td> 0</td>\n",
        "      <td> 4747.533165</td>\n",
        "      <td> 3967.5</td>\n",
        "      <td>  250000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 21,
       "text": [
        "                    age                           monthly_income                       \\\n",
        "                   amin       mean  median  amax            amin         mean  median   \n",
        "serious_dlqin2yrs                                                                       \n",
        "0                    21  52.742980      52   109               0  5463.104630  4500.0   \n",
        "1                    21  45.940302      45    99               0  4747.533165  3967.5   \n",
        "\n",
        "                            \n",
        "                      amax  \n",
        "serious_dlqin2yrs           \n",
        "0                  1794060  \n",
        "1                   250000  "
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###`pandas` also let's you use custom apply functions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def age_x_income(frame):\n",
      "    x = (frame.age * frame.monthly_income)\n",
      "    return np.mean(x)\n",
      "\n",
      "subset.groupby(\"serious_dlqin2yrs\").apply(age_x_income)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 22,
       "text": [
        "serious_dlqin2yrs\n",
        "0                    289098.164793\n",
        "1                    225886.566729"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "###Merging and Joining"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pop = pd.read_csv(\"./data/uspop.csv\")\n",
      "pop"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>age</th>\n",
        "      <th>est_pop</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td> 10</td>\n",
        "      <td> 20055.346939</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td> 11</td>\n",
        "      <td> 20073.020408</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2 </th>\n",
        "      <td> 12</td>\n",
        "      <td> 20090.693878</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td> 13</td>\n",
        "      <td> 20108.367347</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td> 14</td>\n",
        "      <td> 20139.081633</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td> 15</td>\n",
        "      <td> 20912.081633</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6 </th>\n",
        "      <td> 16</td>\n",
        "      <td> 20925.122449</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7 </th>\n",
        "      <td> 17</td>\n",
        "      <td> 20938.163265</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td> 18</td>\n",
        "      <td> 20951.204082</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9 </th>\n",
        "      <td> 19</td>\n",
        "      <td> 20961.326531</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td> 20</td>\n",
        "      <td> 21519.163265</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>11</th>\n",
        "      <td> 21</td>\n",
        "      <td> 21516.244898</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>12</th>\n",
        "      <td> 22</td>\n",
        "      <td> 21513.326531</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>13</th>\n",
        "      <td> 23</td>\n",
        "      <td> 21510.408163</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>14</th>\n",
        "      <td> 24</td>\n",
        "      <td> 21483.408163</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>15</th>\n",
        "      <td> 25</td>\n",
        "      <td> 21333.836735</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>16</th>\n",
        "      <td> 26</td>\n",
        "      <td> 21309.755102</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>17</th>\n",
        "      <td> 27</td>\n",
        "      <td> 21285.673469</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>18</th>\n",
        "      <td> 28</td>\n",
        "      <td> 21261.591837</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>19</th>\n",
        "      <td> 29</td>\n",
        "      <td> 21237.510204</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>20</th>\n",
        "      <td> 29</td>\n",
        "      <td> 20182.673469</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>21</th>\n",
        "      <td> 30</td>\n",
        "      <td> 20163.346939</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>22</th>\n",
        "      <td> 31</td>\n",
        "      <td> 20144.020408</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>23</th>\n",
        "      <td> 32</td>\n",
        "      <td> 20124.693878</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>24</th>\n",
        "      <td> 33</td>\n",
        "      <td> 20105.367347</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>25</th>\n",
        "      <td> 34</td>\n",
        "      <td> 20113.224490</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>26</th>\n",
        "      <td> 35</td>\n",
        "      <td> 19309.367347</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>27</th>\n",
        "      <td> 36</td>\n",
        "      <td> 19336.551020</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>28</th>\n",
        "      <td> 37</td>\n",
        "      <td> 19363.734694</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>29</th>\n",
        "      <td> 38</td>\n",
        "      <td> 19390.918367</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30</th>\n",
        "      <td> 39</td>\n",
        "      <td> 19446.714286</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>31</th>\n",
        "      <td> 40</td>\n",
        "      <td> 20644.224490</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>32</th>\n",
        "      <td> 41</td>\n",
        "      <td> 20672.836735</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33</th>\n",
        "      <td> 42</td>\n",
        "      <td> 20701.448980</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>34</th>\n",
        "      <td> 43</td>\n",
        "      <td> 20730.061224</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>35</th>\n",
        "      <td> 44</td>\n",
        "      <td> 20758.183673</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>36</th>\n",
        "      <td> 45</td>\n",
        "      <td> 21988.020408</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>37</th>\n",
        "      <td> 46</td>\n",
        "      <td> 21987.530612</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>38</th>\n",
        "      <td> 47</td>\n",
        "      <td> 21987.040816</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>39</th>\n",
        "      <td> 48</td>\n",
        "      <td> 21986.551020</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>40</th>\n",
        "      <td> 49</td>\n",
        "      <td> 21936.857143</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>41</th>\n",
        "      <td> 50</td>\n",
        "      <td> 21866.591837</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>42</th>\n",
        "      <td> 51</td>\n",
        "      <td> 21817.387755</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>43</th>\n",
        "      <td> 52</td>\n",
        "      <td> 21768.183673</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>44</th>\n",
        "      <td> 53</td>\n",
        "      <td> 21718.979592</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>45</th>\n",
        "      <td> 54</td>\n",
        "      <td> 21626.428571</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>46</th>\n",
        "      <td> 55</td>\n",
        "      <td> 19467.306122</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>47</th>\n",
        "      <td> 56</td>\n",
        "      <td> 19423.959184</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>48</th>\n",
        "      <td> 57</td>\n",
        "      <td> 19380.612245</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>49</th>\n",
        "      <td> 58</td>\n",
        "      <td> 19337.265306</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>50</th>\n",
        "      <td> 59</td>\n",
        "      <td> 19293.918367</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>51</th>\n",
        "      <td> 59</td>\n",
        "      <td> 17322.448980</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>52</th>\n",
        "      <td> 60</td>\n",
        "      <td> 17214.897959</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>53</th>\n",
        "      <td> 61</td>\n",
        "      <td> 17107.346939</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>54</th>\n",
        "      <td> 62</td>\n",
        "      <td> 16999.795918</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>55</th>\n",
        "      <td> 63</td>\n",
        "      <td> 16892.244898</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>56</th>\n",
        "      <td> 64</td>\n",
        "      <td> 16725.387755</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>57</th>\n",
        "      <td> 65</td>\n",
        "      <td> 12041.387755</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>58</th>\n",
        "      <td> 66</td>\n",
        "      <td> 11982.081633</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>59</th>\n",
        "      <td> 67</td>\n",
        "      <td> 11922.775510</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>60</th>\n",
        "      <td> 68</td>\n",
        "      <td> 11863.469388</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>61</th>\n",
        "      <td> 69</td>\n",
        "      <td> 11759.959184</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>62</th>\n",
        "      <td> 70</td>\n",
        "      <td>  9165.591837</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>63</th>\n",
        "      <td> 71</td>\n",
        "      <td>  9121.387755</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>64</th>\n",
        "      <td> 72</td>\n",
        "      <td>  9077.183673</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>65</th>\n",
        "      <td> 73</td>\n",
        "      <td>  9032.979592</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>66</th>\n",
        "      <td> 74</td>\n",
        "      <td>  8960.836735</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>67</th>\n",
        "      <td> 75</td>\n",
        "      <td>  7032.122449</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>68</th>\n",
        "      <td> 76</td>\n",
        "      <td>  7004.183673</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>69</th>\n",
        "      <td> 77</td>\n",
        "      <td>  6976.244898</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>70</th>\n",
        "      <td> 78</td>\n",
        "      <td>  6948.306122</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>71</th>\n",
        "      <td> 79</td>\n",
        "      <td>  6904.816327</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>72</th>\n",
        "      <td> 80</td>\n",
        "      <td>  5687.897959</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>73</th>\n",
        "      <td> 81</td>\n",
        "      <td>  5672.346939</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>74</th>\n",
        "      <td> 82</td>\n",
        "      <td>  5656.795918</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>75</th>\n",
        "      <td> 83</td>\n",
        "      <td>  5641.244898</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>76</th>\n",
        "      <td> 84</td>\n",
        "      <td>  5625.693878</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>77</th>\n",
        "      <td> 85</td>\n",
        "      <td>  4957.000000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 23,
       "text": [
        "    age       est_pop\n",
        "0    10  20055.346939\n",
        "1    11  20073.020408\n",
        "2    12  20090.693878\n",
        "3    13  20108.367347\n",
        "4    14  20139.081633\n",
        "5    15  20912.081633\n",
        "6    16  20925.122449\n",
        "7    17  20938.163265\n",
        "8    18  20951.204082\n",
        "9    19  20961.326531\n",
        "10   20  21519.163265\n",
        "11   21  21516.244898\n",
        "12   22  21513.326531\n",
        "13   23  21510.408163\n",
        "14   24  21483.408163\n",
        "15   25  21333.836735\n",
        "16   26  21309.755102\n",
        "17   27  21285.673469\n",
        "18   28  21261.591837\n",
        "19   29  21237.510204\n",
        "20   29  20182.673469\n",
        "21   30  20163.346939\n",
        "22   31  20144.020408\n",
        "23   32  20124.693878\n",
        "24   33  20105.367347\n",
        "25   34  20113.224490\n",
        "26   35  19309.367347\n",
        "27   36  19336.551020\n",
        "28   37  19363.734694\n",
        "29   38  19390.918367\n",
        "30   39  19446.714286\n",
        "31   40  20644.224490\n",
        "32   41  20672.836735\n",
        "33   42  20701.448980\n",
        "34   43  20730.061224\n",
        "35   44  20758.183673\n",
        "36   45  21988.020408\n",
        "37   46  21987.530612\n",
        "38   47  21987.040816\n",
        "39   48  21986.551020\n",
        "40   49  21936.857143\n",
        "41   50  21866.591837\n",
        "42   51  21817.387755\n",
        "43   52  21768.183673\n",
        "44   53  21718.979592\n",
        "45   54  21626.428571\n",
        "46   55  19467.306122\n",
        "47   56  19423.959184\n",
        "48   57  19380.612245\n",
        "49   58  19337.265306\n",
        "50   59  19293.918367\n",
        "51   59  17322.448980\n",
        "52   60  17214.897959\n",
        "53   61  17107.346939\n",
        "54   62  16999.795918\n",
        "55   63  16892.244898\n",
        "56   64  16725.387755\n",
        "57   65  12041.387755\n",
        "58   66  11982.081633\n",
        "59   67  11922.775510\n",
        "60   68  11863.469388\n",
        "61   69  11759.959184\n",
        "62   70   9165.591837\n",
        "63   71   9121.387755\n",
        "64   72   9077.183673\n",
        "65   73   9032.979592\n",
        "66   74   8960.836735\n",
        "67   75   7032.122449\n",
        "68   76   7004.183673\n",
        "69   77   6976.244898\n",
        "70   78   6948.306122\n",
        "71   79   6904.816327\n",
        "72   80   5687.897959\n",
        "73   81   5672.346939\n",
        "74   82   5656.795918\n",
        "75   83   5641.244898\n",
        "76   84   5625.693878\n",
        "77   85   4957.000000"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cols = ['age', 'monthly_income', 'serious_dlqin2yrs']\n",
      "result = pd.merge(df[cols] , pop, how='left', on='age')\n",
      "result"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 26,
       "text": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "Int64Index: 116624 entries, 0 to 116623\n",
        "Data columns:\n",
        "age                  116624  non-null values\n",
        "monthly_income       116624  non-null values\n",
        "serious_dlqin2yrs    116624  non-null values\n",
        "est_pop              115075  non-null values\n",
        "dtypes: float64(2), int64(2)"
       ]
      }
     ],
     "prompt_number": 26
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "len(result) > len(df)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 28,
       "text": [
        "True"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.value_counts(pop.age).head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 29,
       "text": [
        "59    2\n",
        "29    2\n",
        "85    1\n",
        "84    1\n",
        "83    1"
       ]
      }
     ],
     "prompt_number": 29
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pop = pop[pop.age.duplicated()==False]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 30
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cols = ['age', 'monthly_income', 'serious_dlqin2yrs']\n",
      "joined = pd.merge(df[cols] , pop, how='left', on='age')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 31
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pop.tail()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>age</th>\n",
        "      <th>est_pop</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>73</th>\n",
        "      <td> 81</td>\n",
        "      <td> 5672.346939</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>74</th>\n",
        "      <td> 82</td>\n",
        "      <td> 5656.795918</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>75</th>\n",
        "      <td> 83</td>\n",
        "      <td> 5641.244898</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>76</th>\n",
        "      <td> 84</td>\n",
        "      <td> 5625.693878</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>77</th>\n",
        "      <td> 85</td>\n",
        "      <td> 4957.000000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 32,
       "text": [
        "    age      est_pop\n",
        "73   81  5672.346939\n",
        "74   82  5656.795918\n",
        "75   83  5641.244898\n",
        "76   84  5625.693878\n",
        "77   85  4957.000000"
       ]
      }
     ],
     "prompt_number": 32
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "joined.est_pop = joined.est_pop.fillna(4957.0)\n",
      "joined.est_pop.describe()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 33,
       "text": [
        "count    112919.000000\n",
        "mean      17881.920711\n",
        "std        4877.586508\n",
        "min        4957.000000\n",
        "25%       16892.244898\n",
        "50%       19467.306122\n",
        "75%       21510.408163\n",
        "max       21988.020408"
       ]
      }
     ],
     "prompt_number": 33
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##`pandasql`\n",
      "Training wheels for `pandas`. We developed and open sourced `pandasql` to help people coming from other languages ease into the `pandas` syntax. It allows you to query `pandas` data frames like they were `SQL` tables."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from pandasql import sqldf\n",
      "pysqldf = lambda q: sqldf(q, globals())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 34
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "query = \"\"\"\n",
      "select\n",
      "    serious_dlqin2yrs\n",
      "    , sum(1) as total\n",
      "from\n",
      "    df\n",
      "group by\n",
      "    serious_dlqin2yrs;\n",
      "\"\"\"\n",
      "pysqldf(query)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>serious_dlqin2yrs</th>\n",
        "      <th>total</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> 105381</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td>   7538</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 35,
       "text": [
        "   serious_dlqin2yrs   total\n",
        "0                  0  105381\n",
        "1                  1    7538"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "query = \"\"\"\n",
      "select\n",
      "    age\n",
      "    , avg(serious_dlqin2yrs) as pct_delinquent\n",
      "from\n",
      "    df\n",
      "group by\n",
      "    age\n",
      "order by\n",
      "    age;\n",
      "\"\"\"\n",
      "pysqldf(query)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>age</th>\n",
        "      <th>pct_delinquent</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td>  21</td>\n",
        "      <td> 0.069231</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td>  22</td>\n",
        "      <td> 0.074405</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2 </th>\n",
        "      <td>  23</td>\n",
        "      <td> 0.108163</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td>  24</td>\n",
        "      <td> 0.114238</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td>  25</td>\n",
        "      <td> 0.129395</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td>  26</td>\n",
        "      <td> 0.116869</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6 </th>\n",
        "      <td>  27</td>\n",
        "      <td> 0.120603</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7 </th>\n",
        "      <td>  28</td>\n",
        "      <td> 0.124785</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td>  29</td>\n",
        "      <td> 0.106615</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9 </th>\n",
        "      <td>  30</td>\n",
        "      <td> 0.110811</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td>  31</td>\n",
        "      <td> 0.099553</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>11</th>\n",
        "      <td>  32</td>\n",
        "      <td> 0.114509</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>12</th>\n",
        "      <td>  33</td>\n",
        "      <td> 0.112634</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>13</th>\n",
        "      <td>  34</td>\n",
        "      <td> 0.101281</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>14</th>\n",
        "      <td>  35</td>\n",
        "      <td> 0.114687</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>15</th>\n",
        "      <td>  36</td>\n",
        "      <td> 0.092789</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>16</th>\n",
        "      <td>  37</td>\n",
        "      <td> 0.092534</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>17</th>\n",
        "      <td>  38</td>\n",
        "      <td> 0.087774</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>18</th>\n",
        "      <td>  39</td>\n",
        "      <td> 0.098048</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>19</th>\n",
        "      <td>  40</td>\n",
        "      <td> 0.087325</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>20</th>\n",
        "      <td>  41</td>\n",
        "      <td> 0.099873</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>21</th>\n",
        "      <td>  42</td>\n",
        "      <td> 0.087538</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>22</th>\n",
        "      <td>  43</td>\n",
        "      <td> 0.085833</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>23</th>\n",
        "      <td>  44</td>\n",
        "      <td> 0.072800</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>24</th>\n",
        "      <td>  45</td>\n",
        "      <td> 0.081794</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>25</th>\n",
        "      <td>  46</td>\n",
        "      <td> 0.089509</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>26</th>\n",
        "      <td>  47</td>\n",
        "      <td> 0.079829</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>27</th>\n",
        "      <td>  48</td>\n",
        "      <td> 0.069759</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>28</th>\n",
        "      <td>  49</td>\n",
        "      <td> 0.082076</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>29</th>\n",
        "      <td>  50</td>\n",
        "      <td> 0.076814</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>30</th>\n",
        "      <td>  51</td>\n",
        "      <td> 0.074835</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>31</th>\n",
        "      <td>  52</td>\n",
        "      <td> 0.072421</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>32</th>\n",
        "      <td>  53</td>\n",
        "      <td> 0.073657</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>33</th>\n",
        "      <td>  54</td>\n",
        "      <td> 0.069465</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>34</th>\n",
        "      <td>  55</td>\n",
        "      <td> 0.064679</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>35</th>\n",
        "      <td>  56</td>\n",
        "      <td> 0.058497</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>36</th>\n",
        "      <td>  57</td>\n",
        "      <td> 0.053250</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>37</th>\n",
        "      <td>  58</td>\n",
        "      <td> 0.048480</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>38</th>\n",
        "      <td>  59</td>\n",
        "      <td> 0.050000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>39</th>\n",
        "      <td>  60</td>\n",
        "      <td> 0.045214</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>40</th>\n",
        "      <td>  61</td>\n",
        "      <td> 0.050395</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>41</th>\n",
        "      <td>  62</td>\n",
        "      <td> 0.046750</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>42</th>\n",
        "      <td>  63</td>\n",
        "      <td> 0.033014</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>43</th>\n",
        "      <td>  64</td>\n",
        "      <td> 0.030078</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>44</th>\n",
        "      <td>  65</td>\n",
        "      <td> 0.039474</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>45</th>\n",
        "      <td>  66</td>\n",
        "      <td> 0.030769</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>46</th>\n",
        "      <td>  67</td>\n",
        "      <td> 0.033546</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>47</th>\n",
        "      <td>  68</td>\n",
        "      <td> 0.021779</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>48</th>\n",
        "      <td>  69</td>\n",
        "      <td> 0.018330</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>49</th>\n",
        "      <td>  70</td>\n",
        "      <td> 0.032787</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>50</th>\n",
        "      <td>  71</td>\n",
        "      <td> 0.025748</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>51</th>\n",
        "      <td>  72</td>\n",
        "      <td> 0.026899</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>52</th>\n",
        "      <td>  73</td>\n",
        "      <td> 0.026910</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>53</th>\n",
        "      <td>  74</td>\n",
        "      <td> 0.026197</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>54</th>\n",
        "      <td>  75</td>\n",
        "      <td> 0.019129</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>55</th>\n",
        "      <td>  76</td>\n",
        "      <td> 0.021229</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>56</th>\n",
        "      <td>  77</td>\n",
        "      <td> 0.017115</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>57</th>\n",
        "      <td>  78</td>\n",
        "      <td> 0.024548</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>58</th>\n",
        "      <td>  79</td>\n",
        "      <td> 0.024032</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>59</th>\n",
        "      <td>  80</td>\n",
        "      <td> 0.017493</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>60</th>\n",
        "      <td>  81</td>\n",
        "      <td> 0.010563</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>61</th>\n",
        "      <td>  82</td>\n",
        "      <td> 0.029046</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>62</th>\n",
        "      <td>  83</td>\n",
        "      <td> 0.023622</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>63</th>\n",
        "      <td>  84</td>\n",
        "      <td> 0.014286</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>64</th>\n",
        "      <td>  85</td>\n",
        "      <td> 0.010811</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>65</th>\n",
        "      <td>  86</td>\n",
        "      <td> 0.018750</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>66</th>\n",
        "      <td>  87</td>\n",
        "      <td> 0.018519</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>67</th>\n",
        "      <td>  88</td>\n",
        "      <td> 0.025974</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>68</th>\n",
        "      <td>  89</td>\n",
        "      <td> 0.033333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>69</th>\n",
        "      <td>  90</td>\n",
        "      <td> 0.014184</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>70</th>\n",
        "      <td>  91</td>\n",
        "      <td> 0.034188</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>71</th>\n",
        "      <td>  92</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>72</th>\n",
        "      <td>  93</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>73</th>\n",
        "      <td>  94</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>74</th>\n",
        "      <td>  95</td>\n",
        "      <td> 0.033333</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>75</th>\n",
        "      <td>  96</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>76</th>\n",
        "      <td>  97</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>77</th>\n",
        "      <td>  98</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>78</th>\n",
        "      <td>  99</td>\n",
        "      <td> 0.125000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>79</th>\n",
        "      <td> 101</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>80</th>\n",
        "      <td> 102</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>81</th>\n",
        "      <td> 103</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>82</th>\n",
        "      <td> 109</td>\n",
        "      <td> 0.000000</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 36,
       "text": [
        "    age  pct_delinquent\n",
        "0    21        0.069231\n",
        "1    22        0.074405\n",
        "2    23        0.108163\n",
        "3    24        0.114238\n",
        "4    25        0.129395\n",
        "5    26        0.116869\n",
        "6    27        0.120603\n",
        "7    28        0.124785\n",
        "8    29        0.106615\n",
        "9    30        0.110811\n",
        "10   31        0.099553\n",
        "11   32        0.114509\n",
        "12   33        0.112634\n",
        "13   34        0.101281\n",
        "14   35        0.114687\n",
        "15   36        0.092789\n",
        "16   37        0.092534\n",
        "17   38        0.087774\n",
        "18   39        0.098048\n",
        "19   40        0.087325\n",
        "20   41        0.099873\n",
        "21   42        0.087538\n",
        "22   43        0.085833\n",
        "23   44        0.072800\n",
        "24   45        0.081794\n",
        "25   46        0.089509\n",
        "26   47        0.079829\n",
        "27   48        0.069759\n",
        "28   49        0.082076\n",
        "29   50        0.076814\n",
        "30   51        0.074835\n",
        "31   52        0.072421\n",
        "32   53        0.073657\n",
        "33   54        0.069465\n",
        "34   55        0.064679\n",
        "35   56        0.058497\n",
        "36   57        0.053250\n",
        "37   58        0.048480\n",
        "38   59        0.050000\n",
        "39   60        0.045214\n",
        "40   61        0.050395\n",
        "41   62        0.046750\n",
        "42   63        0.033014\n",
        "43   64        0.030078\n",
        "44   65        0.039474\n",
        "45   66        0.030769\n",
        "46   67        0.033546\n",
        "47   68        0.021779\n",
        "48   69        0.018330\n",
        "49   70        0.032787\n",
        "50   71        0.025748\n",
        "51   72        0.026899\n",
        "52   73        0.026910\n",
        "53   74        0.026197\n",
        "54   75        0.019129\n",
        "55   76        0.021229\n",
        "56   77        0.017115\n",
        "57   78        0.024548\n",
        "58   79        0.024032\n",
        "59   80        0.017493\n",
        "60   81        0.010563\n",
        "61   82        0.029046\n",
        "62   83        0.023622\n",
        "63   84        0.014286\n",
        "64   85        0.010811\n",
        "65   86        0.018750\n",
        "66   87        0.018519\n",
        "67   88        0.025974\n",
        "68   89        0.033333\n",
        "69   90        0.014184\n",
        "70   91        0.034188\n",
        "71   92        0.000000\n",
        "72   93        0.000000\n",
        "73   94        0.000000\n",
        "74   95        0.033333\n",
        "75   96        0.000000\n",
        "76   97        0.000000\n",
        "77   98        0.000000\n",
        "78   99        0.125000\n",
        "79  101        0.000000\n",
        "80  102        0.000000\n",
        "81  103        0.000000\n",
        "82  109        0.000000"
       ]
      }
     ],
     "prompt_number": 36
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##We just did the following\n",
      "\n",
      "- Used `apply` to make custom data transformations\n",
      "- Did `groupby` and aggregate operations using `pandas`\n",
      "- Used both `pandas` and `pandasql` to merge data frames together"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}